package com.ilarele.shopping.server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;

import com.ilarele.shopping.Product;
import com.ilarele.shopping.ProductComment;
import com.ilarele.shopping.ProductInfo;
import com.ilarele.shopping.ProductLocation;
import com.ilarele.shopping.ProductName;
import com.ilarele.shopping.ProductPicture;
import com.ilarele.shopping.ProductTag;

public class MysqlBasicOps {
	private static final String PRODUCTS = "products";
	private static final String PICTURES = "pictures";
	private static final String NAMES = "namesprod";
	private static final String INFO = "infoprod";
	private static final String COMM = "comments";
	private static final String TAGS = "tags";
	private static final String TAGS_PREF = "tags_pref"; // TODO
	private static final String PREFERENCES = "preferences"; // fr1 p1, fr2 p2,

	private Connection serverMysqlConnection;
	private ShoppingServer shoppingServer;

	// connect
	public MysqlBasicOps(ShoppingServer shoppingServer) {
		try {
			this.shoppingServer = shoppingServer;
			this.serverMysqlConnection = DriverManager
					.getConnection(ConnectionConstants.MYSQL_SERVER_NAME
							+ ConnectionConstants.MYSQL_USERNAME + "&"
							+ ConnectionConstants.MYSQL_PASSWORD);
			if (!this.serverMysqlConnection.isClosed())
				System.out.println("Mysql connection succeded");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		this.createTables();
	}

	// create tables
	public void createTables() {
		String picTableCreateQuery = ""; // TODO
		String productsTableCreateQuery = "";
		String infoTableCreateQuery = "";
		String namesTableCreateQuery = "";
		String commTableCreateQuery = ""; // TODO
		String tagsTableCreateQuery = ""; // TODO
		String preferencesTableCreateQuery = ""; // TODO

		productsTableCreateQuery = "create table if not exists "
				+ PRODUCTS
				+ "(id_prod VARCHAR(20), id_client VARCHAR(20), date BIGINT, is_public BOOLEAN)";

		picTableCreateQuery = "create table if not exists " + PICTURES
				+ "(id_prod VARCHAR(20), id_client VARCHAR(20), "
				+ "pic_url VARCHAR(100), "
				+ "id_pic INTEGER NOT NULL AUTO_INCREMENT unique, "
				+ "PRIMARY KEY(id_prod, id_client))";

		namesTableCreateQuery = "create table if not exists " + NAMES
				+ "(id_prod VARCHAR(20), id_client VARCHAR(20), "
				+ "name VARCHAR(40), "
				+ "id_name INTEGER NOT NULL AUTO_INCREMENT unique, "
				+ "PRIMARY KEY(id_prod,id_client))";

		infoTableCreateQuery = "create table if not exists " + INFO
				+ "(id_prod VARCHAR(20), id_client VARCHAR(20), "
				+ "location VARCHAR(50), price FLOAT, rating INTEGER)";

		commTableCreateQuery = "create table if not exists "
				+ COMM
				+ "(id_prod VARCHAR(20), "
				+ "sender_name VARCHAR(20), content TEXT, "
				+ "date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, "
				+ "rate INTEGER, id_comment INTEGER NOT NULL AUTO_INCREMENT primary key)";
		tagsTableCreateQuery = "create table if not exists "
				+ TAGS
				+ "(id_prod VARCHAR(20), id_client VARCHAR(20), "
				+ "tags VARCHAR(40), "
				+ "rate INTEGER, id_tags INTEGER NOT NULL AUTO_INCREMENT unique, "
				+ "PRIMARY KEY(id_prod,id_client))";
		preferencesTableCreateQuery = "create table if not exists "
				+ PREFERENCES
				+ "(id_client VARCHAR(20), friends_list VARCHAR(6000), tags_list VARCHAR(600), "
				+ "PRIMARY KEY(id_client))";

		try {
			Statement sqlInstruction = this.serverMysqlConnection
					.createStatement();
			sqlInstruction.executeUpdate(productsTableCreateQuery);
			sqlInstruction.executeUpdate(picTableCreateQuery);
			sqlInstruction.executeUpdate(namesTableCreateQuery);
			sqlInstruction.executeUpdate(infoTableCreateQuery);
			sqlInstruction.executeUpdate(commTableCreateQuery);
			sqlInstruction.executeUpdate(tagsTableCreateQuery);
			sqlInstruction.executeUpdate(preferencesTableCreateQuery);

			sqlInstruction.close(); // TODO: move close try catch
		} catch (Exception e) {
			e.printStackTrace();
		}
		System.out.println("Tables Created or already exists!");
	}

	public ResultSet getClientPrefRow(String id_client) {
		String querry = "SELECT id_client, friends_list, tags_list from "
				+ PREFERENCES + " where id_client = \"" + id_client + "\"";
		// System.out.println("[getClientPrefRow] " + querry);
		ResultSet rs = this.executeQuerry(querry);
		return rs;
	}

	public void incPrefFriends(ResultSet clientRow, String idClient,
			String idFriend, int points) {
		if (idFriend == null)
			return;
		StringBuffer newFriendsList = new StringBuffer();
		try {
			if (clientRow.next()) {
				boolean exists = false;
				String toParseFriends = clientRow.getString("friends_list");
				if (!toParseFriends.equals("\"\"")) {
					toParseFriends = toParseFriends.replace("\"", "");
					String[] friendsCount = toParseFriends.split(", ");
					for (String friend : friendsCount) {
						String[] idCount = friend.split(" ");
						String id = idCount[0];
						int count = Integer.parseInt(idCount[1]);
						if (id.equals(idFriend)) {
							count += points;
							exists = true;
							shoppingServer.incFriendship(idClient, idFriend,
									points);
						}
						newFriendsList.append(id);
						newFriendsList.append(" ");
						newFriendsList.append(count);
						newFriendsList.append(", ");
					}
				}
				if (!exists) {
					newFriendsList.append(idFriend);
					newFriendsList.append(" ");
					newFriendsList.append(points);
					newFriendsList.append(", ");
					shoppingServer.incFriendship(idClient, idFriend, points);
				}

				String updateQuerry = "UPDATE " + PREFERENCES
						+ " set friends_list=\"" + newFriendsList.toString()
						+ "\"  where id_client=\"" + idClient + "\";";
				// System.out.println(updateQuerry);
				Statement sqlInstruction = this.serverMysqlConnection
						.createStatement();
				sqlInstruction.executeUpdate(updateQuerry);

				sqlInstruction.close(); // TODO: move close try catch

			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void incPrefTags(ResultSet clientRow, String idClient,
			String newTag, int rate) {
		StringBuffer newTagsList = new StringBuffer();
		try {
			if (clientRow.next()) {
				boolean exists = false;
				String toParseTags = clientRow.getString("tags_list");
				if (!toParseTags.equals("\"\"")) {
					toParseTags = toParseTags.replace("\"", "");
					String[] tagsCount = toParseTags.split(", ");
					for (String tag : tagsCount) {
						String[] nameCount = tag.split(" ");
						System.out.println("tag=" + tag + ":" + nameCount[0]
								+ "|" + nameCount[1]);
						String tagName = nameCount[0];
						int tagCount = Integer.parseInt(nameCount[1]);
						if (tagName.equals(newTag)) {
							tagCount += rate;
							exists = true;
						}
						newTagsList.append(tagName);
						newTagsList.append(" ");
						newTagsList.append(tagCount);
						newTagsList.append(", ");
					}
				}
				if (!exists) {
					newTagsList.append(newTag);
					newTagsList.append(" ");
					newTagsList.append(rate);
					newTagsList.append(", ");
				}
				String updateQuerry = "UPDATE " + PREFERENCES
						+ " set tags_list=\"" + newTagsList.toString()
						+ "\" where id_client=\"" + idClient + "\";";
				// System.out.println(updateQuerry);
				Statement sqlInstruction = this.serverMysqlConnection
						.createStatement();
				sqlInstruction.executeUpdate(updateQuerry);

				sqlInstruction.close(); // TODO: move close try catch
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	// insert into PRODUCTS
	public void addNewProduct(String id_prod, String id_owner) {
		String querry = "INSERT INTO " + PRODUCTS
				+ "(id_prod, id_client, date) VALUES(\"" + id_prod + "\", \""
				+ id_owner + "\"," + System.currentTimeMillis() + ")";
		// System.out.println("[addNewProduct] query " + querry);
		try {
			executeUpdate(querry);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// insert into PICTURES
	public void addPicture(String id_prod, String id_client, String pictureURL) {
		String querry = "INSERT INTO " + PICTURES
				+ "(id_prod, id_client, pic_url) VALUES(\"" + id_prod
				+ "\", \"" + id_client + "\", \"" + pictureURL
				+ "\") ON DUPLICATE KEY UPDATE id_prod=\"" + id_prod + "\"";
		// System.out.println("[addPicture] query " + querry);
		try {
			executeUpdate(querry);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// insert into NAMES
	public void addName(String id_prod, String id_client, String name) {
		String querry = "INSERT INTO " + NAMES
				+ "(id_prod, id_client, name) VALUES(\"" + id_prod + "\", \""
				+ id_client + "\", \"" + name
				+ "\") ON DUPLICATE KEY UPDATE name=\"" + name + "\"";
		// System.out.println("[addName] query " + querry);
		try {
			executeUpdate(querry);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// insert into INFO
	public void addInfo(String id_prod, String id_client,
			ProductLocation pLocation, float price, int rating) {
		String querry = "INSERT INTO "
				+ INFO
				+ "(id_prod, id_client, location, latitude, longitude, price, rating) VALUES(\""
				+ id_prod + "\", \"" + id_client + "\", \"" + pLocation.name
				+ "\", " + pLocation.latitude + ", " + pLocation.longitude
				+ ", " + price + ", " + rating
				+ ") ON DUPLICATE KEY UPDATE location=\"" + pLocation.name
				+ "\", latitude=" + pLocation.latitude + " , longitude="
				+ pLocation.longitude + ", price=" + price + ", rating="
				+ rating;
		// System.out.println("[addInfo] query " + querry);
		try {
			executeUpdate(querry);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// insert into TAGS
	public void addTags(String id_prod, String idClient, String tags) {
		String querry = "INSERT INTO " + TAGS
				+ "(id_prod, id_client, tags) VALUES(\"" + id_prod + "\", \""
				+ idClient + "\", \"" + tags
				+ "\") ON DUPLICATE KEY UPDATE tags=\"" + tags + "\"";
		// System.out.println("[addTag] query " + querry);

		try {
			executeUpdate(querry);
			String[] tagsArray = tags.split(" ");
			renewTagsRelations(tagsArray);
		} catch (Exception e) {
			e.printStackTrace();
		}

		updatePrefTags(idClient, tags, 1);
	}

	private void renewTagsRelations(String[] tags) {
		for (String tag1 : tags) {
			if (shoppingServer.getTagPref(tag1) == null) {
				if (tags.length == 1) {
					saveTagsPrefToDB(tag1, new HashMap<String, Float>(), 1);
					shoppingServer.saveTagPref(tag1, null, 1);
				} else {
					saveTagsPrefToDB(tag1, new HashMap<String, Float>(), 0);
					shoppingServer.saveTagPref(tag1, null, 0);
				}
			}
			HashMap<String, Float> tagsPref = shoppingServer
					.getTagsPrefForTag(tag1);
			for (String tag2 : tags) {
				if (!tag1.equals(tag2)) {
					Float points = tagsPref.get(tag2);
					if (points != null) {
						tagsPref.put(tag2, points + 1);
					} else
						tagsPref.put(tag2, 1f);
				}
			}
			shoppingServer.incTagOccurrence(tag1);
			saveTagsPrefToDB(tag1, tagsPref, 1);
		}
	}

	private void saveTagsPrefToDB(String tag, HashMap<String, Float> tagsPref,
			int occurrence) {
		// t1 5, t3 4
		String tagsString = tagsPref.toString().replace("{", "")
				.replace("}", "").replace("=", " ");
		// in database
		String querry = "INSERT INTO " + TAGS_PREF
				+ " (tag_id, related_tags, occurrence) VALUES (\"" + tag
				+ "\", \"" + tagsString + "\", " + occurrence
				+ ") ON DUPLICATE KEY UPDATE tag_id=\"" + tag
				+ "\" , related_tags=\"" + tagsString
				+ "\" , occurrence=occurrence+" + occurrence;
		// System.out.println("[addTag] query " + querry);
		try {
			executeUpdate(querry);
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	private void updatePrefTags(String idClient, String tags, int rate) {
		// update in prefs - tags
		String[] tagVector = tags.split(" ");
		try {
			for (String tag : tagVector) {
				ResultSet rsClient = getClientPrefRow(idClient);
				if (tag.equals("")) {
					rsClient.close();
					continue;
				}
				incPrefTags(rsClient, idClient, tag, rate);
				if (!rsClient.isClosed())
					rsClient.close();
			}
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
	}

	// disconnect
	public void closeConnection() {
		if (this.serverMysqlConnection != null) {
			try {
				this.serverMysqlConnection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	// drop tables
	public void dropTables() {
		String picQuery = "drop table " + PICTURES;
		String namesQuery = "drop table " + NAMES;
		String infoQuery = "drop table " + INFO;
		String productsQuery = "drop table " + PRODUCTS;
		String commQuery = "drop table " + COMM;
		String tagsQuery = "drop table " + TAGS;

		try {
			Statement sqlInstruction = this.serverMysqlConnection
					.createStatement();
			sqlInstruction.executeUpdate(picQuery);
			sqlInstruction.executeUpdate(namesQuery);
			sqlInstruction.executeUpdate(infoQuery);
			sqlInstruction.executeUpdate(productsQuery);
			sqlInstruction.executeUpdate(commQuery);
			sqlInstruction.executeUpdate(tagsQuery);
			sqlInstruction.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		System.out.println("Tables removed!");
	}

	// wrapper executeQuerry
	public ResultSet executeQuerry(String querry) {
		Statement sqlInstruction = null;
		ResultSet result = null;
		try {
			sqlInstruction = this.serverMysqlConnection.createStatement();
			result = sqlInstruction.executeQuery(querry);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	// wrapper executeUpdate
	public ResultSet executeUpdate(String querry) {
		Statement sqlInstruction = null;
		ResultSet result = null;
		try {
			sqlInstruction = this.serverMysqlConnection.createStatement();
			sqlInstruction.executeUpdate(querry,
					Statement.RETURN_GENERATED_KEYS);
			result = sqlInstruction.getGeneratedKeys();
		} catch (Exception e) {
			e.printStackTrace();
			try {
				sqlInstruction.close();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}

		return result;
	}

	public boolean existsProd(String idProd, Product product) {
		boolean result = false;
		String querry = "SELECT id_prod, date from " + PRODUCTS
				+ " where id_prod = \"" + idProd + "\"";
		// System.out.println("[existsProd] " + querry);
		ResultSet rs = this.executeQuerry(querry);
		try {
			if (rs.next()) {
				product.idProd = idProd;
				product.date = rs.getLong("date");
				fillPic(product);
				fillInfo(product);
				fillName(product);
				fillTags(product);
				result = true;
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		updateDate(idProd);
		return result;
	}

	private void updateDate(String idProd) {
		String querry = "UPDATE " + PRODUCTS + " set date="
				+ System.currentTimeMillis() + " where id_prod=\"" + idProd
				+ "\"";
		// System.out.println("[updateDate] " + querry);
		ResultSet rs = executeUpdate(querry);
		try {
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public ArrayList<Product> getProducts() {
		ArrayList<Product> result = new ArrayList<Product>();
		String querry = "SELECT id_prod from " + PRODUCTS;
		// System.out.println("[getProducts] " + querry);
		ResultSet rs = this.executeQuerry(querry);
		try {
			while (rs.next()) {
				Product prod = new Product();
				prod.idProd = rs.getString("id_prod");
				fillPic(prod);
				fillName(prod);
				fillInfo(prod);
				fillTags(prod);
				result.add(prod);
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	private void fillTags(Product prod) {
		String querry = "SELECT tags, id_tags from " + TAGS
				+ " where id_prod=\"" + prod.idProd + "\" ORDER BY rate DESC";
		// System.out.println("\t[fillTags] " + querry);
		ResultSet rs = this.executeQuerry(querry);

		try {
			if (rs.next()) {
				prod.pTags.tags = rs.getString("tags");
				prod.pTags.id = rs.getString("id_tags");
			}
			rs.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private void fillInfo(Product prod) {
		String querry = "SELECT location, latitude, longitude, price, rating, id_info, rate from "
				+ INFO
				+ " where id_prod=\""
				+ prod.idProd
				+ "\" ORDER BY rate DESC";
		// System.out.println("\t[fillInfo] " + querry);
		ResultSet rs = this.executeQuerry(querry);

		try {
			if (rs.next()) {
				prod.pInfo.pLocation.name = rs.getString("location");
				prod.pInfo.pLocation.latitude = rs.getDouble("latitude");
				prod.pInfo.pLocation.longitude = rs.getDouble("longitude");
				prod.pInfo.price = rs.getFloat("price");
				prod.pInfo.rating = rs.getInt("rating");
				prod.pInfo.id = rs.getString("id_info");
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private void fillPic(Product prod) {
		String querry = "SELECT pic_url, id_pic from " + PICTURES
				+ " where id_prod=\"" + prod.idProd + "\" ORDER BY rate DESC";
		// //System.out.println("\t[fillPic] " + querry);
		ResultSet rs = this.executeQuerry(querry);

		try {
			if (rs.next()) {
				prod.pPicture.imageUrl = rs.getString("pic_url");
				prod.pPicture.id = rs.getString("id_pic");
			}
			rs.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private void fillName(Product prod) {
		String querry = "SELECT name, id_name from " + NAMES
				+ " where id_prod=\"" + prod.idProd + "\" ORDER BY rate DESC";
		// //System.out.println("\t[fillName] " + querry);
		ResultSet rs = this.executeQuerry(querry);

		try {
			if (rs.next()) {
				prod.pName.name = rs.getString("name");
				prod.pName.id = rs.getString("id_name");
			}
			rs.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void saveProd(Product prod, String idClient) {
		addName(prod.idProd, idClient, prod.pName.name);
		addInfo(prod.idProd, idClient, prod.pInfo.pLocation, prod.pInfo.price,
				prod.pInfo.rating);
		addTags(prod.idProd, idClient, prod.pTags.tags);
		addPicture(prod.idProd, idClient, prod.pPicture.imageUrl);
	}

	private void updatePrefFriends(String idClient, String idFriend, int dbRate) {
		boolean isPublic = true;
		// check if friend data are public
		String querry = "SELECT is_public from " + PREFERENCES
				+ " where id_client=\"" + idClient + "\" LIMIT 0, 1";
		// //System.out.println("\t[updatePrefFriends] " + querry);
		ResultSet rs = this.executeQuerry(querry);

		try {
			if (rs.next()) {
				isPublic = rs.getBoolean("is_public");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (!isPublic)
			return;

		// then update in prefs
		ResultSet rsClient = getClientPrefRow(idClient);
		incPrefFriends(rsClient, idClient, idFriend, dbRate);

		try {
			rsClient.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void updateProdInfo(ProductInfo pi, String idClient) {
		String productId = null;
		if (pi.dbRate != 0) {
			// update tags
			String querry = "SELECT id_prod from " + INFO + " where id_info="
					+ pi.id;
			// System.out.println(querry);
			ResultSet rs = executeQuerry(querry);
			try {
				if (rs.next()) {
					productId = rs.getString("id_prod");
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}

			// update rate
			querry = "UPDATE " + INFO + " set rate=rate+" + pi.dbRate
					+ " where id_info=" + pi.id;
			// System.out.println(querry);
			rs = executeUpdate(querry);
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

			String idFriend = getInfoCommiter(pi.id);
			updatePrefFriends(idClient, idFriend, pi.dbRate);
			if (productId != null) {
				StringBuilder tagsBuilder = new StringBuilder();
				HashMap<String, Float> tagsMap = getTagsForProduct(productId);
				for (String tag : tagsMap.keySet()) {
					if (tagsMap.get(tag) < 0)
						continue;
					tagsBuilder.append(tag + " ");
				}
				updatePrefTags(idClient, tagsBuilder.toString(), 1);
			}
		}
	}

	public void updateProdName(ProductName pn, String idClient) {
		String productId = null;
		if (pn.dbRate != 0) {
			// update tags
			String querry = "SELECT id_prod from " + NAMES + " where id_name="
					+ pn.id;
			// System.out.println(querry);
			ResultSet rs = executeQuerry(querry);
			try {
				if (rs.next()) {
					productId = rs.getString("id_prod");
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}

			// update in names
			querry = "UPDATE " + NAMES + " set rate=rate+" + pn.dbRate
					+ " where id_name=" + pn.id;
			// System.out.println(querry);
			rs = executeUpdate(querry);

			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

			String idFriend = getNameCommiter(pn.id);
			updatePrefFriends(idClient, idFriend, pn.dbRate);
			if (productId != null) {
				StringBuilder tagsBuilder = new StringBuilder();
				HashMap<String, Float> tagsMap = getTagsForProduct(productId);
				for (String tag : tagsMap.keySet()) {
					if (tagsMap.get(tag) < 0)
						continue;
					tagsBuilder.append(tag + " ");
				}
				updatePrefTags(idClient, tagsBuilder.toString(), 1);
			}
		}
	}

	public void updateProdTags(ProductTag pt, String idClient) {
		if (pt.dbRate != 0) {
			// update in names
			String querry = "UPDATE " + TAGS + " set rate=rate+" + pt.dbRate
					+ " where id_tags=" + pt.id;
			// System.out.println(querry);
			ResultSet rs = executeUpdate(querry);
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

			String idFriend = getTagsCommiter(pt.id);
			updatePrefFriends(idClient, idFriend, pt.dbRate);
		}
	}

	public void updateProdPicture(ProductPicture pp, String idClient) {
		String productId = null;
		if (pp.dbRate != 0) {
			// update tags
			String querry = "SELECT id_prod from " + PICTURES
					+ " where id_pic=" + pp.id;
			// System.out.println(querry);
			ResultSet rs = executeQuerry(querry);
			try {
				if (rs.next()) {
					productId = rs.getString("id_prod");
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}

			// update in names
			querry = "UPDATE " + PICTURES + " set rate=rate+" + pp.dbRate
					+ " where id_pic=" + pp.id;
			// System.out.println(querry);
			rs = executeUpdate(querry);
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			// update in prefs
			String idFriend = getPictureCommiter(pp.id);
			updatePrefFriends(idClient, idFriend, pp.dbRate);
			if (productId != null) {
				StringBuilder tagsBuilder = new StringBuilder();
				HashMap<String, Float> tagsMap = getTagsForProduct(productId);
				for (String tag : tagsMap.keySet()) {
					if (tagsMap.get(tag) < 0)
						continue;
					tagsBuilder.append(tag + " ");
				}
				updatePrefTags(idClient, tagsBuilder.toString(), 1);
			}
		}
	}

	private String getPictureCommiter(String id) {
		String idClient = null;
		String querry = "SELECT id_client from " + PICTURES
				+ " WHERE id_pic=\"" + id + "\" LIMIT 0, 1";
		// //System.out.println("\t[getPictureCommiter] " + querry);
		ResultSet rs = this.executeQuerry(querry);
		try {
			if (rs.next()) {
				idClient = rs.getString("id_client");
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return idClient;
	}

	private String getNameCommiter(String id) {
		String idClient = null;
		String querry = "SELECT id_client from " + NAMES + " WHERE id_name=\""
				+ id + "\" LIMIT 0, 1";
		// //System.out.println("\t[getNameCommiter] " + querry);
		ResultSet rs = this.executeQuerry(querry);
		try {
			if (rs.next()) {
				idClient = rs.getString("id_client");
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return idClient;
	}

	private String getTagsCommiter(String id) {
		String idClient = null;
		String querry = "SELECT id_client from " + TAGS + " WHERE id_tags=\""
				+ id + "\" LIMIT 0, 1";
		// System.out.println("\t[getTagsCommiter] " + querry);
		ResultSet rs = this.executeQuerry(querry);
		try {
			if (rs.next()) {
				idClient = rs.getString("id_client");
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return idClient;
	}

	private String getInfoCommiter(String id) {
		String idClient = null;
		String querry = "SELECT id_client from " + INFO + " WHERE id_info=\""
				+ id + "\" LIMIT 0, 1";
		// //System.out.println("\t[getInfoCommiter] " + querry);
		ResultSet rs = this.executeQuerry(querry);
		try {
			if (rs.next()) {
				idClient = rs.getString("id_client");
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return idClient;
	}

	public boolean getNextProdName(int dir, Product prod) {
		boolean result = false;
		int oldRate = 0;
		String compStr = "<";
		String order = "DESC";
		if (dir < 0) {
			compStr = ">";
			order = "";
			System.out.println("find before " + prod.pName.id);
		} else
			System.out.println("find after " + prod.pName.id);

		String querry = "SELECT rate, id_name from " + NAMES
				+ " WHERE id_name=\"" + prod.pName.id + "\" LIMIT 0, 1";
		// //System.out.println("\t[getNextProdName] " + querry);
		ResultSet rs = this.executeQuerry(querry);
		try {
			if (rs.next()) {
				oldRate = rs.getInt("rate");
			}
			rs.close();
			querry = "SELECT name, id_name from " + NAMES + " WHERE id_prod=\""
					+ prod.idProd + "\" and rate" + compStr + "=" + oldRate
					+ " and id_name <>" + prod.pName.id + "  ORDER BY rate "
					+ order + " LIMIT 0, 1";
			// System.out.println("\t[getNextProdName] " + querry);
			rs = this.executeQuerry(querry);

			if (rs.next()) {
				System.out.println("new name " + rs.getString("name") + " id="
						+ rs.getString("id_name"));
				prod.pName.name = rs.getString("name");
				prod.pName.id = rs.getString("id_name");
				result = true;
			}
			rs.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public void insertComment(Product prod, String idClient) {
		String querry = "INSERT INTO " + COMM
				+ "(id_prod, content, sender_name, id_client) VALUES(\""
				+ prod.idProd + "\", \"" + prod.pComments.content + "\", \""
				+ prod.pComments.senderName + "\", \"" + idClient + "\")";
		// System.out.println("[insertComment] query " + querry);
		try {
			executeUpdate(querry);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public ArrayList<ProductComment> getComments(String idProd) {
		ArrayList<ProductComment> result = new ArrayList<ProductComment>();
		String querry = "SELECT content, sender_name, date from " + COMM
				+ " where id_prod=\"" + idProd + "\" ORDER BY date DESC";
		// System.out.println("[getComments] " + querry);
		ResultSet rs = this.executeQuerry(querry);
		try {
			while (rs.next()) {
				ProductComment comm = new ProductComment();
				comm.content = rs.getString("content");
				comm.senderName = rs.getString("sender_name");
				comm.date = rs.getString("date");
				result.add(comm);
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public boolean getNextProdInfo(int dir, Product prod) {
		boolean result = false;
		int oldRate = 0;
		String compStr = "<";
		String order = "DESC";
		if (dir < 0) {
			compStr = ">";
			order = "";
			System.out.println("find before " + prod.pInfo.id);
		} else
			System.out.println("find after " + prod.pInfo.id);

		String querry = "SELECT rate, location, price, rating, id_info from "
				+ INFO + " WHERE id_info=\"" + prod.pInfo.id + "\" LIMIT 0, 1";
		// System.out.println("\t[getNextProdInfo] " + querry);
		ResultSet rs = this.executeQuerry(querry);
		try {
			if (rs.next()) {
				oldRate = rs.getInt("rate");
			}
			rs.close();
			querry = "SELECT location, price, rating, id_info from " + INFO
					+ " WHERE id_prod=\"" + prod.idProd + "\" and rate"
					+ compStr + "=" + oldRate + " and id_info<>"
					+ prod.pInfo.id + "  ORDER BY rate " + order
					+ " LIMIT 0, 1";
			// System.out.println("\t[getNextProdInfo] " + querry);
			rs = this.executeQuerry(querry);

			if (rs.next()) {
				System.out.println("new price " + rs.getFloat("price") + " id="
						+ rs.getString("id_info"));
				prod.pInfo.price = rs.getFloat("price");
				prod.pInfo.rating = rs.getInt("rating");
				prod.pInfo.pLocation.name = rs.getString("location");
				prod.pInfo.pLocation.latitude = rs.getDouble("latitude");
				prod.pInfo.pLocation.longitude = rs.getDouble("longitude");
				prod.pInfo.id = rs.getString("id_info");
				result = true;
			}
			rs.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public boolean getNextProdTags(int dir, Product prod) {
		boolean result = false;
		int oldRate = 0;
		String compStr = "<";
		String order = "DESC";
		if (dir < 0) {
			compStr = ">";
			order = "";
			System.out.println("find before " + prod.pTags.tags);
		} else
			System.out.println("find after " + prod.pTags.tags);

		String querry = "SELECT rate, id_tags from " + TAGS
				+ " WHERE id_tags=\"" + prod.pTags.id + "\" LIMIT 0, 1";
		// System.out.println("\t[getNextProdTags] " + querry);
		ResultSet rs = this.executeQuerry(querry);
		try {
			if (rs.next()) {
				oldRate = rs.getInt("rate");
			}
			rs.close();
			querry = "SELECT tags, id_tags from " + TAGS + " WHERE id_prod=\""
					+ prod.idProd + "\" and rate" + compStr + "=" + oldRate
					+ " and id_tags<>" + prod.pTags.id + "  ORDER BY rate "
					+ order + " LIMIT 0, 1";
			// System.out.println("\t[getNextProdTags] " + querry);
			rs = this.executeQuerry(querry);

			if (rs.next()) {
				System.out.println("new tags " + rs.getString("tags") + " id="
						+ rs.getString("id_tags"));
				prod.pTags.tags = rs.getString("tags");
				prod.pTags.id = rs.getString("id_tags");
				result = true;
			}
			rs.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public boolean getNextProdPic(int dir, Product prod) {
		boolean result = false;
		int oldRate = 0;
		String compStr = "<";
		String order = "DESC";
		if (dir < 0) {
			compStr = ">";
			order = "";
			System.out.println("find before " + prod.pPicture.imageUrl);
		} else
			System.out.println("find after " + prod.pPicture.imageUrl);

		String querry = "SELECT rate, id_pic from " + PICTURES
				+ " WHERE id_pic=\"" + prod.pPicture.id + "\" LIMIT 0, 1";
		// System.out.println("\t[getNextProdPic] " + querry);
		ResultSet rs = this.executeQuerry(querry);
		try {
			if (rs.next()) {
				oldRate = rs.getInt("rate");
			}
			rs.close();
			querry = "SELECT pic_url, id_pic from " + PICTURES
					+ " WHERE id_prod=\"" + prod.idProd + "\" and rate"
					+ compStr + "=" + oldRate + " and id_pic<>"
					+ prod.pPicture.id + "  ORDER BY rate " + order
					+ " LIMIT 0, 1";
			// System.out.println("\t[getNextProdPic] " + querry);
			rs = this.executeQuerry(querry);

			if (rs.next()) {
				System.out.println("new pic " + rs.getString("pic_url")
						+ " id=" + rs.getString("id_pic"));
				prod.pPicture.imageUrl = rs.getString("pic_url");
				prod.pPicture.id = rs.getString("id_pic");
				result = true;
			}
			rs.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	// public void addPointsByFriendship(ArrayList<Product> prodList,
	// String idClient) {
	// // search in friend list for idClient
	// // id - points
	// HashMap<String, Float> friends = getFriends(idClient);
	// if (friends == null)
	// return;
	// System.out.println("FRIENDS LIST");
	// for (Entry<String, Float> entry : friends.entrySet()) {
	// System.out.println(entry.getKey() + " -- " + entry.getValue());
	// }
	// if (friends != null) {
	// // add points to an product for each detail found, submited by a
	// // friend
	// for (Product product : prodList) {
	// HashSet<String> interfIds = getUsersIdInterferedWith(product);
	// for (String id : interfIds) {
	// Float points = friends.get(id);
	// if (points != null) {
	// product.points += points;
	// System.out.println("points for " + id + "===+===> "
	// + points + "/" + product.points);
	// }
	// }
	// }
	// }
	//
	// }
	//
	// private HashMap<String, Float> getFriends(String idClient) {
	// String querry = "SELECT friends_list from " + PREFERENCES
	// + " WHERE id_client=\"" + idClient + "\" LIMIT 0, 1";
	// //System.out.println(querry);
	// ResultSet rs = this.executeQuerry(querry);
	// String toParse = null;
	// try {
	// if (rs.next()) {
	// toParse = rs.getString("friends_list");
	// }
	// rs.close();
	// } catch (SQLException e) {
	// e.printStackTrace();
	// if (rs != null)
	// try {
	// rs.close();
	// } catch (SQLException e1) {
	// e1.printStackTrace();
	// }
	// }
	//
	// return parseFriends(toParse);
	// }

	public HashMap<String, Float> parseFriends(String toParse) {
		HashMap<String, Float> friends = null;
		if (toParse != null && !toParse.equals("\"\"")) {
			toParse = toParse.replace("\"", "");
			// System.out.println("friends list: " + toParse);
			friends = new HashMap<String, Float>();
			String[] pairs = toParse.split(", ");
			for (String pair : pairs) {
				String[] splitedPair = pair.split(" ");
				float friendTrust = Float.parseFloat(splitedPair[1]);
				friends.put(splitedPair[0], friendTrust);
			}
		}
		return friends;
	}

	// private HashSet<String> getUsersIdInterferedWith(Product product) {
	// HashSet<String> users = new HashSet<String>();
	// // comments
	// // getFieldId("id_client");
	//
	// // INFO
	// addUsersId(users, INFO, "id_prod", "id_client", product.idProd);
	// // NAMES
	// addUsersId(users, NAMES, "id_prod", "id_client", product.idProd);
	// // PICTURES
	// addUsersId(users, PICTURES, "id_prod", "id_client", product.idProd);
	// // PRODUCTS
	// addUsersId(users, PRODUCTS, "id_prod", "id_client", product.idProd);
	// // TAGS
	// addUsersId(users, TAGS, "id_prod", "id_client", product.idProd);
	// return users;
	// }

	// private void addUsersId(HashSet<String> users, String table,
	// String comparedColumn, String resultColumn, String expectedValue) {
	// String querry = "SELECT " + resultColumn + " from " + table + " WHERE "
	// + comparedColumn + "=\"" + expectedValue + "\"";
	// ResultSet rs = this.executeQuerry(querry);
	// try {
	// while (rs.next()) {
	// users.add(rs.getString(resultColumn));
	// }
	// rs.close();
	// } catch (SQLException e) {
	// e.printStackTrace();
	// if (rs != null)
	// try {
	// rs.close();
	// } catch (SQLException e1) {
	// e1.printStackTrace();
	// }
	// }
	// }

	/**
	 * map (tag_name, points_from_votes)
	 * 
	 * @param productId
	 * @return
	 */
	public HashMap<String, Float> getTagsForProduct(String productId) {
		HashMap<String, Float> result = new HashMap<String, Float>();
		String querry = "SELECT tags, rate from " + TAGS + " WHERE id_prod=\""
				+ productId + "\"";
		// System.out.println(querry);
		ResultSet rs = this.executeQuerry(querry);
		try {
			while (rs.next()) {
				String[] tags = rs.getString("tags").split(" ");
				float rate = rs.getInt("rate");
				for (String tag : tags) {
					Float oldTagPoints = result.get(tag);
					if (oldTagPoints != null) {
						result.put(tag, oldTagPoints + rate);
					} else
						result.put(tag, rate);
				}
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
		}
		return result;
	}

	public HashMap<String, Float> getPrefTags(String idClient) {
		String querry = "SELECT tags_list from " + PREFERENCES
				+ " WHERE id_client=\"" + idClient + "\" LIMIT 0, 1";
		// System.out.println(querry);
		ResultSet rs = this.executeQuerry(querry);
		String toParse = null;
		try {
			if (rs.next()) {
				toParse = rs.getString("tags_list");
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
		}
		return parseTagsList(toParse);
	}

	public ResultSet getPreferencesRS() {
		String querry = "SELECT * from " + PREFERENCES;
		// System.out.println(querry);
		return this.executeQuerry(querry);
	}

	public Float getInteresRate(String idUser, String idProd) {
		Float interes = 0.f;
		// check in new products
		if (checkExists(PRODUCTS, idUser, idProd))
			interes += 2;
		// check comments
		if (checkExists(COMM, idUser, idProd))
			interes += 2;

		// for each table with rates
		interes += getRateForTable(NAMES, idUser, idProd);
		interes += getRateForTable(PICTURES, idUser, idProd);
		interes += getRateForTable(INFO, idUser, idProd);
		interes += getRateForTable(TAGS, idUser, idProd);

		return interes;
	}

	private boolean checkExists(String table, String idUser, String idProd) {
		String querry = "SELECT id_client from " + table
				+ " WHERE id_client=\"" + idUser + "\" and id_prod=\"" + idProd
				+ "\" LIMIT 0, 1";
		ResultSet rs = executeQuerry(querry);
		try {
			return rs.next();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	private int getRateForTable(String table, String idUser, String idProd) {
		int rate = 0;
		String querry = "SELECT rate from " + table + " WHERE id_client=\""
				+ idUser + "\" and id_prod=\"" + idProd + "\" LIMIT 0, 1";
		ResultSet rs = executeQuerry(querry);
		try {
			while (rs.next()) {
				rate += Math.abs(rs.getInt("rate"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rate;
	}

	public ResultSet getTagsRS() {
		String querry = "SELECT * from " + TAGS_PREF;
		// System.out.println(querry);
		return this.executeQuerry(querry);
	}

	public HashMap<String, Float> parseTagsList(String toParse) {
		HashMap<String, Float> tags = null;
		if (toParse != null && !toParse.equals("\"\"")) {
			toParse = toParse.replace("\"", "");
			tags = new HashMap<String, Float>();
			String[] pairs = toParse.split(", ");
			for (String pair : pairs) {
				String[] splitedPair = pair.split(" ");
				tags.put(splitedPair[0], Float.parseFloat(splitedPair[1]));
			}
		}
		return tags;
	}

	private void addUsersInTouchWithProdFromTable(HashSet<String> users,
			String table, String idProd) {
		String querry = "SELECT id_client from " + table + " WHERE id_prod=\""
				+ idProd + "\"";
		ResultSet rs = executeQuerry(querry);
		try {
			while (rs.next()) {
				users.add(rs.getString("id_client"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public HashSet<String> getUsersInTouchWithProd(String idProd) {
		HashSet<String> users = new HashSet<String>();
		addUsersInTouchWithProdFromTable(users, PRODUCTS, idProd);
		addUsersInTouchWithProdFromTable(users, COMM, idProd);
		addUsersInTouchWithProdFromTable(users, TAGS, idProd);
		addUsersInTouchWithProdFromTable(users, PICTURES, idProd);
		addUsersInTouchWithProdFromTable(users, INFO, idProd);
		addUsersInTouchWithProdFromTable(users, NAMES, idProd);
		return users;
	}

	public void addUser(String idClient) {
		String querry = "INSERT IGNORE INTO " + PREFERENCES
				+ "(id_client) VALUES(\"" + idClient + "\")";
		// System.out.println("[addUser] query " + querry);
		try {
			executeUpdate(querry);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void setUserPrivacy(String idClient, boolean isPublic) {
		String updateQuerry = "UPDATE " + PREFERENCES + " set is_public=\""
				+ isPublic + "\"  where id_client=\"" + idClient + "\";";
		// System.out.println(updateQuerry);
		Statement sqlInstruction;
		try {
			sqlInstruction = this.serverMysqlConnection.createStatement();
			sqlInstruction.executeUpdate(updateQuerry);
			sqlInstruction.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}